<?php
$page_type = "A";
require_once 'header.php';

if (!empty($_GET["update_success"]))
  $update_success = "&nbsp<font color=\"#CC0000\">{$_GET["update_success"]}</font>";
else
  $update_success = '';

if (!empty($_GET["query_error"]))
  $msg = "<font color=\"#CC0000\"><b>&nbsp{$_GET["query_error"]}</b></font>";
else
  $msg = "";	
  
echo "<h2>Student Management Main</h2>";

// Generate the query section
echo("
  <form method=\"post\" action=\"student.php\">
  SID: <input type=\"text\" size=\"20\" maxlength=\"10\" name=\"input_sid\"> 
  First Name: <input type=\"text\" size=\"20\" maxlength=\"30\" name=\"input_fname\">
  Last Name: <input type=\"text\" size=\"20\" maxlength=\"30\" name=\"input_lname\"><br><br>
  Course Number: <input type=\"text\" size=\"20\" maxlength=\"10\" name=\"input_cno\">
  ");
  
// Dropdown menu for student type
echo("
  Student Type:
  <select name=\"input_student_type\">
    <option value=\"\">All</option>
    <option value=\"U\">Undergraduate</option>
    <option value=\"G\">Graduate</option></select>
  ");
  
// Dropdown menu for probation status
echo("
  Probation Status:
  <select name=\"input_pflag\">
    <option value=\"\">All</option>
    <option value=\"y\">Yes</option>
    <option value=\"n\">No</option>
  ");
  
$new = "true";

echo("
  </select>
  <input type=\"submit\" value=\"Search\">$msg
  </form><br>
  <form method=\"post\" action=\"student_add.php?new=$new\"><br>
  </form>
  ");
  
// Interpret the query requirements
$input_sid = "";
$input_cno = "";
$input_student_type = "";
$input_pflag = "";
$input_fname = "";
$input_lname = "";

if (isset($_POST["input_sid"])) 
  $input_sid = $_POST["input_sid"];
if (isset($_POST["input_cno"])) 
  $input_cno = $_POST["input_cno"];
if (isset($_POST["input_student_type"])) 
  $input_student_type = $_POST["input_student_type"];
if (isset($_POST["input_pflag"])) 
  $input_pflag = $_POST["input_pflag"];
if (isset($_POST["input_fname"])) 
  $input_fname = $_POST["input_fname"];
if (isset($_POST["input_lname"])) 
  $input_lname = $_POST["input_lname"];

$whereClause = " C.Sid=T.Sid and T.Seqid=CO.Seqid ";

// Add inputted student id to where clause
if (isset($input_sid) and trim($input_sid) != "") { 
  $whereClause .= " and C.Sid like '%$input_sid%'"; 
}
// Add inputted course number to where clause
if (isset($input_cno) and trim($input_cno) != "") { 
  $whereClause .= " and Cno like '%$input_cno%'"; 
}
// Add inputted first name to where clause
if (isset($input_fname) and trim($input_fname) != "") { 
  $whereClause .= " and Fname like '%$input_fname%'"; 
}
// Add inputted last name to where clause
if (isset($input_lname) and trim($input_lname) != "") { 
  $whereClause .= " and Lname like '%$input_lname%'"; 
}
// Add inputted student type to where clause
if (isset($input_student_type) and $input_student_type != "") { 
  if ($input_student_type == "U")
    $whereClause .= " and STFlag='0'"; 
  else if ($input_student_type == "G")
    $whereClause .= " and STFlag='1'"; 
}
// Add inputted probation status to where clause
if (isset($input_pflag) and $input_pflag != "") { 
  if ($input_pflag == "n")
    $whereClause .= " and PFlag='0'"; 
  else if ($input_pflag == "y")
    $whereClause .= " and PFlag='1'"; 
}

// Form the query statement array and run it
$sql = "select Fname, Lname, C.Sid, STFlag, PFlag, Cno, Grade, T.Seqid 
        from CLIENT C, TAKEN T, COURSE_OFFERING CO where $whereClause";

$result_array = execute_sql_in_oracle($sql);
$result = $result_array["flag"];
$cursor = $result_array["cursor"];

if (!$result) {
  $query_error = "Student query failed!";
  header("Location:student.php?query_error=$query_error");
}
else {
  // Display the query results
  echo "<table border=1>";
  echo "<tr> <th>SID</th> <th>First Name</th> <th>Last Name</th> <th>Student Type</th> <th>Probation Status</th> <th>Course SeqID</th> <th>Course Number</th> <th>Grade</th>
        <th>Update</th> <th>Enter Grade</th>";

  // Fetch the result from the cursor one by one
  while ($values = oci_fetch_array($cursor)) {
  
    $fname = $values[0];
    $lname = $values[1];
    $sid = $values[2];
	$stflag = $values[3];
	$pflag = $values[4];
	$cno = $values[5];
	$grade = $values[6];
	$seqid = $values[7];
	
	// Student type
	if (isset($stflag) and $stflag == '0')
      $input_student_type = "Undergraduate";
    else if ($stflag == '1')
      $input_student_type = "Graduate";
    else
      $input_student_type = "";
	  
	// Probation Status
	if ($pflag == '0')
      $input_pflag = "No";
    else if ($pflag == '1')
      $input_pflag = "Yes";
	else
      $input_pflag = "";
  
    echo("<tr>" . 
      "<td>$sid</td> <td>$fname</td> <td>$lname</td> <td>$input_student_type</td> <td>$input_pflag</td> <td>$seqid</td> <td>$cno</td> <td>$grade</td>".
      " <td> <A HREF=\"student_update.php?sid=$sid&seqid=$seqid\">Update</A> </td> ".
	  " <td> <A HREF=\"grade_update.php?sid=$sid&seqid=$seqid\">Enter Grade</A> </td> ".
      "</tr>");
  }
  oci_free_statement($cursor);
  echo "</table>";
}
?>
</body>
</html>